<?php if( ! defined('BASEPATH')) exit('No direct script access allowed');

    class Post_m extends CI_Model {
        public function __construct() {
            
        }
/*-------------------------------------------------------------------------------------------------*/
/*-------------------------------------------GET METHODS-------------------------------------------*/
/*-------------------------------------------------------------------------------------------------*/
        function get_max_post(){
            $this->db->select_max('post_id');
            $query = $this->db->get('post');
            
            return $query->row_array();
        }
        
        function get_all_bulletin_post() {
            $this->db->select('user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
            YEAR(post.date_time_pub) AS year, 
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
            TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
            post.author, post.status FROM bulletin_post JOIN post ON 
            post.post_id = bulletin_post.post_id JOIN user ON post.author = user.user_id
            WHERE post.status="published" AND bulletin_post.post_id = post.post_id ORDER BY post.date_time_pub DESC');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function count_bulletin_post(){
            $this->db->select('user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
            YEAR(post.date_time_pub) AS year, 
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
            TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
            post.author, post.status FROM bulletin_post JOIN post ON 
            post.post_id = bulletin_post.post_id JOIN user ON post.author = user.user_id
            WHERE post.status="published" AND bulletin_post.post_id = post.post_id ORDER BY post.date_time_pub DESC');
            
            $query = $this->db->get();
            
            return $query->num_rows;
        }
        
        function all_group_post($user_id) {
            $this->db->select('user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
                YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status');
            $this->db->from('user');
            $this->db->join('subscriber', 'user.user_id = subscriber.user_id');
            $this->db->join('groups', 'subscriber.group_id = groups.group_id');
            $this->db->join('group_post', 'groups.group_id = group_post.group_id');
            $this->db->join('post', 'group_post.post_id = post.post_id');
            #$this->db->join('bulletin_post', 'post.post_id = bulletin_post.post_id');
            $this->db->where('post.status', 'published');
            $this->db->where('user.user_id', $user_id);
            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function count_group_post($user_id) {
            
            $this->db->select('user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
                YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status');
            $this->db->from('user');
            $this->db->join('subscriber', 'user.user_id = subscriber.user_id');
            $this->db->join('groups', 'subscriber.group_id = groups.group_id');
            $this->db->join('group_post', 'groups.group_id = group_post.group_id');
            $this->db->join('post', 'group_post.post_id = post.post_id');
            #$this->db->join('bulletin_post', 'post.post_id = bulletin_post.post_id');
            $this->db->where('post.status', 'published');
            $this->db->where('user.user_id', $user_id);
            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->num_rows;
        }
      
        function get_bulletin_group_posts($user_id){
            /*$query = $this->db->query('SELECT post.post_id,post.title,post.summary,
              post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
              MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
              groups.group_name FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
              group_post USING(group_id) JOIN post USING(post_id)
              WHERE user_id = '.$user_id.' 
              UNION  
              SELECT post.post_id,post.title,post.summary,
              post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
              MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,"Bulletin" FROM post JOIN bulletin_post USING(post_id)');*/
            
            $query = $this->db->query('SELECT * FROM
            (SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
            CONCAT(GROUP_CONCAT(groups.group_name SEPARATOR " / ")," / ", IF(bulletin_post.post_id IS NULL,"","Bulletin")) AS lokasyon
            FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
            group_post USING(group_id) JOIN post USING(post_id) LEFT JOIN bulletin_post USING(post_id)
            WHERE user_id = '.$user_id.' AND post.status="published" GROUP BY(post.post_id)) AS p1 
            UNION 
            SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,"Bulletin" AS lokasyon
            FROM post JOIN bulletin_post USING(post_id)
            WHERE post.post_id NOT IN (SELECT post_id FROM group_post) AND post.status="published" GROUP BY post.post_id)AS p2)
            as finalP');
            
            /*
             * 
             */
            return $query->result_array();
        }
        
        function count_bulletin_group_posts($user_id){
            /*$query = $this->db->query('SELECT post.post_id,post.title,post.summary,
              post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
              MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
              groups.group_name FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
              group_post USING(group_id) JOIN post USING(post_id)
              WHERE user_id = '.$user_id.' 
              UNION  
              SELECT post.post_id,post.title,post.summary,
              post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
              MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,"Bulletin" FROM post JOIN bulletin_post USING(post_id)');*/
            
            $query = $this->db->query('SELECT * FROM
            (SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
            CONCAT(GROUP_CONCAT(groups.group_name SEPARATOR " / ")," / ", IF(bulletin_post.post_id IS NULL,"","Bulletin")) AS lokasyon
            FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
            group_post USING(group_id) JOIN post USING(post_id) LEFT JOIN bulletin_post USING(post_id)
            WHERE user_id = '.$user_id.' AND post.status="published" GROUP BY(post.post_id)) AS p1 
            UNION 
            SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,"Bulletin" AS lokasyon
            FROM post JOIN bulletin_post USING(post_id)
            WHERE post.post_id NOT IN (SELECT post_id FROM group_post) AND post.status="published" GROUP BY post.post_id)AS p2)
            as finalP');
            
            /*
             * 
             */
            return $query->num_rows;
        }
        
        function get_all_bulletin_and_group_post() {
            $this->db->select("user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
                    YEAR(post.date_time_pub) AS year, 
                    MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                    TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                    post.author, post.status");
            $this->db->from('user');
            $this->db->join('subscriber', 'user.user_id = subscriber.user_id');
            $this->db->join('groups', 'subscriber.group_id = groups.group_id');
            $this->db->join('group_post', 'groups.group_id = group_post.group_id');
            $this->db->join('post', 'group_post.post_id = post.post_id');
            $this->db->join('bulletin_post', 'post.post_id = bulletin_post.post_id');
            $this->db->where('bulletin_post.post_id', 'post.post_id');
            $this->db->where('group_post.post_id', 'post.post_id');
            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function get_year() {
            $query = "SELECT DISTINCT YEAR(post.date_time_pub) AS years FROM post WHERE post.status = 'published'";
            $q = $this->db->query($query);
            return $q->result_array(); 
        }
        
        function get_bulletin_post($post_id) {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status, post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->join('bulletin_post', 'post.post_id = bulletin_post.post_id');
            $this->db->where('post.status', 'published');
            //$this->db->where('destination_id', 1);
            $this->db->where('post_id', $post_id);
            $query = $this->db->get();
            
            return $query->row_array();
        }
        
        function get_bulletin_by_group($group_id, $user_id) {
            
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status, post.state');
            $this->db->from('user');
            $this->db->join('subscriber', 'user.user_id = subscriber.user_id');
            $this->db->join('groups', 'subscriber.group_id = groups.group_id');
            $this->db->join('group_post', 'groups.group_id = group_post.group_id');
            $this->db->join('post', 'group_post.post_id = post.post_id');
            $this->db->where('post.status', 'published');
            $this->db->where('subscriber.group_id', $group_id);
            $this->db->where('user.user_id', $user_id);
            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function check_bulletin_by_month($month_name, $year) {
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('post.status', 'published');
            $this->db->where('MONTHNAME(date_time_pub)',  strtoupper ($month_name));
            $this->db->where('YEAR(date_time_pub)',  strtoupper ($year));
            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->num_rows;
        }
        
        function get_bulletin_by_month($month_name, $year) {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status, post.state');
            $this->db->from('user');
            $this->db->join('post', 'post.author = user.user_id');
            $this->db->join('bulletin_post', 'bulletin_post.post_id = post.post_id');
            $this->db->where('post.status', 'published');
            $this->db->where('MONTHNAME(date_time_pub)',  strtoupper ($month_name));
            $this->db->where('YEAR(date_time_pub)',  strtoupper ($year));
            $this->db->where('bulletin_post.post_id', 'post.post_id');
            $this->db->order_by('date_time_pub', 'desc');
//            
//            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
//                user.email, user.password, post.post_id, post.title, post.summary, 
//                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
//                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
//                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
//                post.author, post.status, post.state');
//            $this->db->from('post');
//            $this->db->join('user', 'post.author = user.user_id');
//            $this->db->where('post.status', 'published');
//            $this->db->where('MONTHNAME(date_time_pub)',  strtoupper ($month_name));
//            $this->db->where('YEAR(date_time_pub)',  strtoupper ($year));
//            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->result();
        }
        
        function get_bulletin_by_group_month($group_id, $month_name, $year) {
            
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status, post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->join('group_post', 'post.post_id = group_post.post_id');
            $this->db->where('post.status', 'published');
            $this->db->where('MONTHNAME(date_time_pub)', strtoupper($month_name));
            $this->db->where('YEAR(date_time_pub)', $year);
            $this->db->where('group_post.group_id', $group_id);
            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function check_bulletin_by_group_month($group_id, $month_name, $year) {
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->join('group_post', 'post.post_id = group_post.post_id');
            $this->db->where('post.status', 'published');
            $this->db->where('MONTHNAME(date_time_pub)', strtoupper($month_name));
            $this->db->where('YEAR(date_time_pub)', $year);
            $this->db->where('group_post.group_id', $group_id);
            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->num_rows;
        }
        
        function get_bulletin_by_year($year) {
            
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status, post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('post.status', 'published');
            $this->db->where('YEAR(date_time_pub)', $year);
            $this->db->order_by('date_time_pub', 'desc');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function get_bulletin_full_text_post($post_id) {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status, post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('post.status', 'published');
            $this->db->where('post_id', $post_id);
            $query = $this->db->get();
            
            return $query->row_array();
        }
        
        function get_all_published_post() {
            $query_str = "
                SELECT user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified, 
                post.category_id, post.author, post.status, 
                post.state,CONCAT(GROUP_CONCAT(groups.group_name SEPARATOR '/ '),'/ ', IF(bulletin_post.post_id IS NULL,'','Bulletin'))
                AS hey FROM bulletin_post
                RIGHT JOIN post USING(post_id) join user ON post.author = user.user_id 
                JOIN group_post ON post.post_id = group_post.post_id JOIN groups ON group_post.group_id = groups.group_id
                where post.status='published' GROUP BY post.post_id
                UNION
                SELECT user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified, 
                post.category_id, post.author, post.status, 
                post.state,'Bulletin' AS hey FROM bulletin_post
                JOIN post USING(post_id) join user ON post.author = user.user_id 
                where post.status='published' AND post.post_id NOT IN (SELECT post_id FROM group_post)GROUP BY post.post_id";
            $query = $this->db->query($query_str);
            
            return $query->result_array();
        }
        
        function get_all_my_post() {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified, 
                post.category_id, post.author, post.status, post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('author', $this->session->userdata('user_id'));
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function get_published_post($post_id) {
            $this->db->select('post_id, title, summary, full_text, status, state');
            $this->db->where('post.status', 'published');
            $this->db->where('post_id', $post_id);
            $query = $this->db->get('post');
            
            return $query->row_array();
        }
        
        function get_all_pending_post() {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified, 
                post.category_id, post.author, post.status, 
                post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('post.status', 'pending');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function get_pending_post($post_id) {
            $this->db->select('post_id, title, summary, full_text, status');
            $this->db->where('post.status', 'pending');
            $this->db->where('post_id', $post_id);
            $query = $this->db->get('post');
            
            return $query->row_array();
        }
        
        function count_pending_post() {
            $this->db->where('post.status', 'pending');
            $query = $this->db->get('post');
            return $query->num_rows();
        }
        
        function get_all_drafted_post($author) {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified, 
                post.category_id, post.author, post.status, 
                post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('author', $author);
            $this->db->where('post.status', 'draft');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function get_drafted_post($post_id) {
            $this->db->select('post_id, title, summary, full_text, date_time_pub, 
                date_time_modified, date_time_deleted, category_id, author, status, state');
            $this->db->where('post.status', 'draft');
            $this->db->where('post_id', $post_id);
            $query = $this->db->get('post');
            
            return $query->row_array();
        }
        
        function count_drafted_post($author) {
            $this->db->where('status', 'draft');
            $this->db->where('author', $author);
            $query = $this->db->get('post');
            return $query->num_rows();
        }
        
        function get_all_trashed_post() {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified,  
                post.date_time_deleted, post.category_id, post.author,
                post.status, post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('post.status', 'deleted');
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function get_trashed_post($post_id) {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified,  
                post.date_time_deleted, post.category_id, post.author,
                post.status, post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('post_id', $post_id);
            $query = $this->db->get();
            
            return $query->row_array();
        }
        
        function count_trashed_post() {
            $this->db->where('post.status', 'deleted');
            $query = $this->db->get('post');
            return $query->num_rows();
        }
        
        function get_all_rejected_post($author){
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified,  
                post.date_time_deleted, post.category_id, post.author, post.status, post.state, 
                notification.comment, notification.notification_id');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->join('notification', 'post.post_id = notification.post_id');
            $this->db->where('author', $author);
            $this->db->where('post.status', 'rejected');
            $query = $this->db->get();
            
            return $query->result_array();
        }

        function get_rejected_post($post_id){
//            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
//                user.email, user.password, post.post_id, post.title, post.summary,
//                post.full_text, post.date_time_pub, post.date_time_modified,  
//                post.date_time_deleted, post.category_id, post.author, post.destination_id, 
//                post.status, post.state');
//            $this->db->from('post');
//            $this->db->join('user', 'post.author = user.user_id');
            #$this->db->join('notification', 'post.post_id = notification.post_id');
            $this->db->where('post_id', $post_id);
           # $this->db->where('status', 'rejected');
            $query = $this->db->get('post');
            
            return $query->row_array();
        }
        
        function count_rejected_post($author) {
            $this->db->where('status', 'rejected');
            $this->db->where('author', $author);
            $query = $this->db->get('post');
            return $query->num_rows();
        }
        
        function sort_draft($order, $author){
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary,
                post.full_text, post.date_time_pub, post.date_time_modified, 
                post.category_id, post.author, post.status, 
                post.state');
            $this->db->from('post');
            $this->db->join('user', 'post.author = user.user_id');
            $this->db->where('author', $author);
            $this->db->where('post.status', 'draft');
            $this->db->order_by('post.title', $order);
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
//        function get_bulletin_group_posts_by_year($year){
//            
//        }
        
        function get_bulletin_posts_by_year($year){
            /*$this->db->select('user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
            YEAR(post.date_time_pub) AS year, 
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
            TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
            post.author, post.status FROM bulletin_post JOIN post ON 
            post.post_id = bulletin_post.post_id JOIN user ON post.author = user.user_id
            WHERE post.status="published" AND bulletin_post.post_id = post.post_id AND 
            YEAR(post.date_time_pub) ='.$year.' ORDER BY post.date_time_pub DESC');
            $query = $this->db->get();*/
            $query = $this->db->query('SELECT * FROM
            (SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
            CONCAT(GROUP_CONCAT(groups.group_name SEPARATOR " / ")," / ", IF(bulletin_post.post_id IS NULL,"","Bulletin")) AS lokasyon
            FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
            group_post USING(group_id) JOIN post USING(post_id) LEFT JOIN bulletin_post USING(post_id)
            WHERE user_id = 1 AND post.status="published" AND YEAR(post.date_time_pub)='.$year.' GROUP BY(post.post_id)) AS p1 
            UNION 
            SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,"Bulletin" AS lokasyon
            FROM post JOIN bulletin_post USING(post_id)
            WHERE post.post_id NOT IN (SELECT post_id FROM group_post) AND post.status="published" AND YEAR(post.date_time_pub)='.$year.' 
             GROUP BY post.post_id)AS p2) as finalP');
            
            return $query->result_array();
        }
        
        function count_posts_by_year($year) {
            $query = $this->db->query('SELECT * FROM
            (SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
            CONCAT(GROUP_CONCAT(groups.group_name SEPARATOR " / ")," / ", IF(bulletin_post.post_id IS NULL,"","Bulletin")) AS lokasyon
            FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
            group_post USING(group_id) JOIN post USING(post_id) LEFT JOIN bulletin_post USING(post_id)
            WHERE user_id = 1 AND post.status="published" AND YEAR(post.date_time_pub)='.$year.' GROUP BY(post.post_id)) AS p1 
            UNION 
            SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,"Bulletin" AS lokasyon
            FROM post JOIN bulletin_post USING(post_id)
            WHERE post.post_id NOT IN (SELECT post_id FROM group_post) AND post.status="published" AND YEAR(post.date_time_pub)='.$year.' 
             GROUP BY post.post_id)AS p2) as finalP');
            
            return $query->num_rows;
            
        }
/*-------------------------------------------------------------------------------------------------*/
/*------------------------------------------INSERT METHODS-----------------------------------------*/
/*-------------------------------------------------------------------------------------------------*/
        function publish_post($data) {
            $this->db->set('post_id', $data['post_id']); 
            $this->db->set('title', $data['title']);
            $this->db->set('summary', $data['summary']);
            $this->db->set('full_text', $data['full_text']);
            $this->db->set('date_time_pub', date('Y-m-d h:i:s'));
            $this->db->set( 'status', $data['status']);
            if($data['status'] !== 'draft') {
                $this->db->set('state', 'show');
            }
            $this->db->set('author', $data['author']);
            $this->db->set('category_id', $data['category_id']);
            $this->db->insert('post');
        }
        
        function save_post($data) {
            $this->db->set('post_id', $data['post_id']); 
            $this->db->set('title', $data['title']);
            $this->db->set('summary',  /*str_replace("../resources", "../../resources", */
                    $data['summary']);
            $this->db->set('full_text', /*str_replace("../resources", "../../resources", */
                    $data['full_text']);
            $this->db->set( 'status', $data['status']);
            if($data['status'] !== 'draft') {
                $this->db->set('state', 'show');
            }
            $this->db->set('author', $data['author']);
            $this->db->set('category_id', $data['category_id']);
            //$this->db->set('destination_id', $data['destination_id']);
            $this->db->insert('post');
        }

        function insert_update_notification($post_id, $data){
            $this->db->set('comment',$data);
            $this->db->set('post_id',$post_id);
            $this->db->insert('notification');

            $this->db->set('status','rejected');
            $this->db->where('post_id',$post_id);
            $this->db->update('post');
        }
/*-------------------------------------------------------------------------------------------------*/
/*------------------------------------------UPDATE METHODS-----------------------------------------*/
/*-------------------------------------------------------------------------------------------------*/
        function update_post($post_id, $data) {
            $this->db->where('post_id', $post_id);
            $this->db->set('title', $data['title']);
            $this->db->set('summary', $data['summary']);
            $this->db->set('full_text', $data['full_text']);
            $this->db->set('state', $data['state']);
            $this->db->set('date_time_modified', date('Y-m-d h:i:s'));
            $this->db->update('post');
        }
        
        function publish_pending_post($post_id, $data) {
            $this->db->where('post_id', $post_id);
            $this->db->set('title', $data['title']);
            $this->db->set('summary', $data['summary']);
            $this->db->set('full_text', $data['full_text']);
            $this->db->set('status', $data['status']);
            $this->db->set('state', $data['state']);
            $this->db->set('date_time_pub', date('Y-m-d h:i:s'));
            $this->db->update('post');
        }
        
        function update_rejected_post($post_id, $data) {
            $this->db->where('post_id', $post_id);
            $this->db->set('title', $data['title']);
            $this->db->set('summary', $data['summary']);
            $this->db->set('full_text', $data['full_text']);
            $this->db->set('status', $data['status']);
            $this->db->set('state', $data['state']);
            $this->db->set('date_time_pub', date('Y-m-d h:i:s'));
            $this->db->update('post');
        }
        
        function publish_drafted_post($post_id, $data) {
            $this->db->where('post_id', $post_id);
            $this->db->set('title', $data['title']);
            $this->db->set('summary', $data['summary']);
            $this->db->set('full_text', $data['full_text']);
            $this->db->set('status', $data['status']);
            $this->db->set('state', $data['state']);
            $this->db->set('date_time_pub', date('Y-m-d h:i:s'));
            $this->db->update('post');
        }
        
        function save_drafted_post($post_id, $data) {
            $this->db->where('post_id', $post_id);
            $this->db->set('title', $data['title']);
            $this->db->set('summary', $data['summary']);
            $this->db->set('full_text', $data['full_text']);
            $this->db->set('status', $data['status']);
            $this->db->set('state', $data['state']);
            $this->db->update('post');
        }
        
        function recover_trashed_post($post_id, $data) {
            $this->db->where('post_id', $post_id);
            $this->db->update('post', $data);
        }
        
        function delete_post($post_id) {
            $this->db->where('post_id', $post_id);
            $this->db->set('date_time_deleted', date('Y-m-d h:i:s'));
            $this->db->set('status', 'deleted');
            $this->db->update('post');
        }
/*-------------------------------------------------------------------------------------------------*/
/*------------------------------------------DELETE METHODS-----------------------------------------*/
/*-------------------------------------------------------------------------------------------------*/
        function delete_drafted_post($post_id) {
            $this->db->where('post_id', $post_id);
            $this->db->delete('post');
        }
        
        function delete_trashed_post($post_id){
            $this->db->where('post_id', $post_id);
            $this->db->delete('post');
        } 
        
        function gpost($id,$group_id){
            $this->db->set('group_id',$group_id);
            $this->db->set('post_id',$id);
            $this->db->insert('group_post');
        }
        
        function bulletin_post($post_id){
            $this->db->set('post_id',$post_id);
            $this->db->insert('bulletin_post');
        }
        
        function get_groups(){
            return $this->db->get('groups');
        }

        function get_category(){
            return $this->db->get('category');
        }
        
        function fetch_all_bulletin_post($limit, $start) {
            
            $query = $this->db->query('SELECT user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
                    YEAR(post.date_time_pub) AS year, 
                    MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                    TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                    post.author,"" AS lokasyon FROM bulletin_post JOIN post ON 
                    post.post_id = bulletin_post.post_id JOIN user ON post.author = user.user_id
                    WHERE post.status="published" ORDER BY YEAR(date_time_pub) DESC LIMIT '.$start.','.$limit);
            /*$this->db->query($query,$this->db->limit($limit, $start));*/
            /*$query = $this->db->get();*/
            
            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
        }
        
        function fetch_all_group_post($limit, $start, $user_id) {
            $this->db->select('user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
                    YEAR(post.date_time_pub) AS year, 
                    MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                    TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                    post.author, post.status FROM user  JOIN groups ON subscriber.group_id = groups.group_id
                    JOIN group_post ON groups.group_id = group_post.group_id
                    JOIN post ON group_post.post_id = post.post_id
                    JOIN bulletin_post ON post.post_id = bulletin_post.post_id
                    WHERE post.status = "published" AND user.user_id ='.$user_id.' 
                    ORDER BY YEAR(date_time_pub) ASC');
            $this->db->limit($limit, $start);
            $query = $this->db->get();
            
            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
        }
        
        function fetch_bulletin_by_group_post($limit, $start, $group_id, $user_id) {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status, post.state FROM user JOIN subscriber ON 
                user.user_id = subscriber.user_id JOIN groups ON subscriber.group_id = groups.group_id
                JOIN group_post ON groups.group_id = group_post.group_id JOIN post ON
                group_post.post_id = post.post_id WHERE post.status = "published" AND 
                subscriber.group_id ='.$group_id.' AND user.user_id ='.$user_id.' ORDER BY date_time_pub DESC');
            $this->db->limit($limit, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
        }
        
        function fetch_bulletin_by_group_month_post($limit, $start, $group_id, $month, $year) {
            $this->db->select('user.user_id, user.fname, user.mname, user.lname, 
                user.email, user.password, post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, YEAR(post.date_time_pub) AS year, 
                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
                post.author, post.status, post.state FROM user JOIN subscriber ON 
                user.user_id = subscriber.user_id JOIN groups ON subscriber.group_id = groups.group_id
                JOIN group_post ON groups.group_id = group_post.group_id JOIN post ON
                group_post.post_id = post.post_id WHERE post.status = "published" AND MONTHNAME(date_time_pub) = 
                "'.strtoupper($month).'" AND YEAR(date_time_pub) = '.strtoupper ($year).' AND
                subscriber.group_id ='.$group_id.' ORDER BY date_time_pub DESC');
            $this->db->limit($limit, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
        }
        
        function fetch_bulletin_by_month_post($limit, $start, $month_name, $year) {
            $this->db->select('user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
                YEAR(post.date_time_pub) AS year, MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) 
                AS day, TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, post.author, 
                post.status FROM bulletin_post JOIN post ON post.post_id = bulletin_post.post_id JOIN user ON 
                post.author = user.user_id WHERE post.status="published" AND MONTHNAME(date_time_pub) = 
                "'.strtoupper($month_name).'" AND YEAR(date_time_pub) = '.strtoupper ($year).' ORDER BY 
                post.date_time_pub DESC');
            $this->db->limit($limit, $start);
            $query = $this->db->get(); 
            
            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
        }
        
//        function fetch_all_group_post($limit, $start, $user_id) {
//            $this->db->select('user.*,post.post_id, post.title,post.summary,post.date_time_pub,post.full_text,
//                YEAR(post.date_time_pub) AS year, 
//                MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day, 
//                TIME(post.date_time_pub) AS time,post.date_time_modified, post.category_id, 
//                post.author, post.status FROM user JOIN subscriber ON user.user_id = subscriber.user_id
//                JOIN groups ON subscriber.group_id = groups.group_id
//                JOIN group_post ON groups.group_id = group_post.group_id
//                JOIN post ON group_post.post_id = post.post_id
//                JOIN bulletin_post ON post.post_id = bulletin_post.post_id
//                WHERE post.status = "published" AND user.user_id ='.$user_id.' 
//                ORDER BY post.date_time_pub DESC');
//            $this->db->limit($limit, $start);
//            $query = $this->db->get();
//            
//            if ($query->num_rows() > 0) {
//                foreach ($query->result() as $row) {
//                    $data[] = $row;
//                }
//                return $data;
//            }
//            return false;
//        }
        
      function get_group_posts_by_year($year,$id,$limit,$start){
        $this->db->select('post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
            groups.group_name FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
            group_post USING(group_id) JOIN post USING(post_id)
            WHERE user_id = '.$id.' AND YEAR(date_time_pub) = '.$year);
        $this->db->limit($limit, $start);
        $query = $this->db->get();
        if($query->num_rows() > 0){
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
      }
      
      function fetch_group_posts($limit,$start,$id){
        $this->db->select('post.post_id,post.title,post.summary,
post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
GROUP_CONCAT(groups.group_name SEPARATOR " / ") AS lokasyon FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
group_post USING(group_id) JOIN post USING(post_id)
WHERE user_id = '.$id.' AND post.status="published" GROUP BY post.post_id ORDER BY YEAR(date_time_pub) DESC');
        $this->db->limit($limit, $start);
        $query = $this->db->get();
        if($query->num_rows() > 0){
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
      }
      
      function fetch_bulletin_group_post($limit, $start, $user_id){
         $query = $this->db->query('SELECT * FROM
            (SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
            CONCAT(GROUP_CONCAT(groups.group_name SEPARATOR " / ")," / ", IF(bulletin_post.post_id IS NULL,"","Bulletin")) AS lokasyon
            FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
            group_post USING(group_id) JOIN post USING(post_id) LEFT JOIN bulletin_post USING(post_id)
            WHERE user_id = '.$user_id.' AND post.status="published" GROUP BY(post.post_id)) AS p1 
            UNION 
            SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,"Bulletin" AS lokasyon
            FROM post JOIN bulletin_post USING(post_id)
            WHERE post.post_id NOT IN (SELECT post_id FROM group_post) AND post.status="published" GROUP BY post.post_id)AS p2)
            as finalP ORDER BY YEAR(date_time_pub) DESC, DAY(date_time_pub) DESC LIMIT '.$start.','.$limit);
        if($query->num_rows() > 0){
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
      }
      
      function fetch_bulletin_posts_by_year($limit, $start, $year,$category,$user_id) {
          $query = $this->db->query('SELECT * FROM
            (SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,
            CONCAT(GROUP_CONCAT(groups.group_name SEPARATOR " / ")," / ", IF(bulletin_post.post_id IS NULL,"","Bulletin")) AS lokasyon
            FROM user JOIN subscriber USING(user_id) JOIN groups USING(group_id) JOIN 
            group_post USING(group_id) JOIN post USING(post_id) LEFT JOIN bulletin_post USING(post_id)
            WHERE user_id = '.$user_id.' AND post.status="published" AND YEAR(post.date_time_pub)='.$year.' GROUP BY(post.post_id)) AS p1 
            UNION 
            SELECT * FROM(SELECT post.post_id,post.title,post.summary,
            post.date_time_pub,post.full_text,YEAR(post.date_time_pub) AS year,
            MONTHNAME(post.date_time_pub) AS month, DAY(post.date_time_pub) AS day,"Bulletin" AS lokasyon
            FROM post JOIN bulletin_post USING(post_id)
            WHERE post.post_id NOT IN (SELECT post_id FROM group_post) AND post.status="published" AND YEAR(post.date_time_pub)='.$year.' 
             GROUP BY post.post_id)AS p2) as finalP ORDER BY DAY(date_time_pub) DESC LIMIT '.$start.','.$limit);
            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
      }
    }
?>
